package studentdemo.dao;

import studentdemo.modul.Student;
import studentdemo.util.DBUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    @Override
    public int insertStudent(Student student) {
        String sql = "INSERT INTO Dujx_Student11 (djx_Sno11, djx_Sname11, djx_Ssex11, djx_Sage11, djx_Srname11, djx_Stotalcredit11, djx_Sclno11) VALUES (?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, student.getSno());
            ps.setString(2, student.getSname());
            ps.setString(3, student.getSsex());
            ps.setInt(4, student.getSage());
            ps.setString(5, student.getSrname());
            ps.setInt(6, student.getStotalcredit());
            ps.setString(7, student.getSclno());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int updateStudent(Student student) {
        String sql = "UPDATE Dujx_Student11 SET djx_Sname11=?, djx_Ssex11=?, djx_Sage11=?, djx_Srname11=?, djx_Stotalcredit11=?, djx_Sclno11=? WHERE djx_Sno11=?";
        try (Connection conn = DBUtil.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, student.getSname());
            ps.setString(2, student.getSsex());
            ps.setInt(3, student.getSage());
            ps.setString(4, student.getSrname());
            ps.setInt(5, student.getStotalcredit());
            ps.setString(6, student.getSclno());
            ps.setString(7, student.getSno());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int deleteStudent(String sno) {
        String sql = "DELETE FROM Dujx_Student11 WHERE djx_Sno11=?";
        try (Connection conn = DBUtil.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, sno);
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public Student getStudentById(String sno) {
        String sql = "SELECT * FROM Dujx_Student11 WHERE djx_Sno11=?";
        try (Connection conn = DBUtil.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, sno);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return extractStudent(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Student> getStudentsByName(String name) {
        String sql = "SELECT * FROM Dujx_Student11 WHERE djx_Sname11 LIKE ?";
        List<Student> list = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, "%" + name + "%");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                list.add(extractStudent(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<Student> getAllStudents(int page, int pageSize) {
        String sql = "SELECT * FROM Dujx_Student11 ORDER BY djx_Sno11 LIMIT ? OFFSET ?";
        List<Student> list = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, pageSize);
            ps.setInt(2, (page - 1) * pageSize);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                list.add(extractStudent(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int getStudentCount() {
        String sql = "SELECT COUNT(*) FROM Dujx_Student11";
        try (Connection conn = DBUtil.getConnection(); Statement st = conn.createStatement()) {
            ResultSet rs = st.executeQuery(sql);
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    private Student extractStudent(ResultSet rs) throws SQLException {
        Student s = new Student();
        s.setSno(rs.getString("djx_Sno11"));
        s.setSname(rs.getString("djx_Sname11"));
        s.setSsex(rs.getString("djx_Ssex11"));
        s.setSage(rs.getInt("djx_Sage11"));
        s.setSrname(rs.getString("djx_Srname11"));
        s.setStotalcredit(rs.getInt("djx_Stotalcredit11"));
        s.setSclno(rs.getString("djx_Sclno11"));
        return s;
    }
} 